﻿using System;
using System.Data;
using System.Data.Common;

namespace FancyIdea.PingShui.Core.DbHelper
{
    /// <summary>
    /// DbCommondWrapper用于封装DbCommmon对象，使数据库操作更便捷
    /// </summary>
    public abstract class DbCommandWrapper : IDisposable
    {
        /// <summary>
        /// 获取或设置一个值，该值表示在该对象被Dispose的时候是否同时Dispose它
        /// 所关联的DbConnection对象。
        /// 一般情况下这个值不应手工改变。
        /// </summary>
        protected bool IsDisposingConnection = false;

        protected bool IsDisposingTransaction = false;
        private DbCommand command;

        /// <summary>
        /// 获取或设置DataReader对象，该DataReader对象是在执行了
        /// ExecuteDataReader之后产生的
        /// </summary>
        public DbDataReader DataReader
        {
            get;
            protected set;
        }

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public DbCommandWrapper()
        {
        }

        /// <summary>
        /// 从已存在的DbCommandWrapper对象构造新的DbCommandWrapper。
        /// parent中的Connection和Transaction等属性会被拷贝到新的对象中。
        /// </summary>
        /// <param name="cmdWrapper"></param>
        public DbCommandWrapper(DbCommandWrapper parent)
            : this()
        {
            if (parent == null)
            {
                return;
            }

            command = CreateCommand();
            command.Connection = parent.command.Connection;
            command.Transaction = parent.command.Transaction;
        }

        #region 提供给子类的接口
        /// <summary>
        /// 创建数据库连接。用于对不同数据库的实现。
        /// </summary>
        /// <returns></returns>
        protected abstract DbConnection CreateConnection();

        protected virtual DbConnection CreateConnection(string connStr)
        {
            DbConnection connection = CreateConnection();
            connection.ConnectionString = connStr;
            return connection;
        }

        /// <summary>
        /// 创建DbDataAdapter对象。用于对不同数据库的实现。
        /// </summary>
        /// <returns></returns>
        protected abstract DbDataAdapter CreateDataAdapter();

        /// <summary>
        /// 创建DbCommand对象。用于对不同数据库的实现。
        /// </summary>
        /// <returns></returns>
        protected abstract DbCommand CreateCommand();

        /// <summary>
        /// 创建DbParameter对象。用于对不同数据库的实现。
        /// </summary>
        /// <returns></returns>
        protected abstract DbParameter CreateParameter();
        #endregion

        #region 设置DbCommand
        public DbCommandWrapper SetCommand()
        {
            CheckCommand();
            this.command = CreateCommand();
            return this;
        }

        public DbCommandWrapper SetCommand(DbCommand command)
        {
            CheckCommand();
            this.command = command;
            return this;
        }

        public DbCommandWrapper SetCommand(string command)
        {
            if (this.command == null)
            {
                this.command = CreateCommand();
            }

            this.command.CommandText = command;
            return this;
        }

        public DbCommandWrapper SetCommand(string command, CommandType type)
        {
            SetCommand(command);
            this.command.CommandType = type;
            return this;
        }

        protected virtual void CheckCommand()
        {
            if (command != null)
            {
                throw new InvalidOperationException(
                    "SetCommand can be invoke only once");
            }
        }
        #endregion

        #region 设置连接
        public DbCommandWrapper SetConnection(DbConnection connection)
        {
            if (command == null)
            {
                SetCommand();
            }
            command.Connection = connection;
            return this;
        }

        public DbCommandWrapper SetConnection(string connectionString)
        {
            DbConnection connection = CreateConnection(connectionString);
            return SetConnection(connection);
        }
        #endregion

        #region 事务处理
        public DbCommandWrapper SetTransaction(DbTransaction transaction)
        {
            CheckIsNotInTransaction();
            if (command == null)
            {
                SetCommand();
            }
            command.Transaction = transaction;
            IsDisposingTransaction = false;
            return this;
        }

        public DbCommandWrapper BeginTransaction()
        {
            CheckIsNotInTransaction();
            OpenConnection();
            command.Transaction = command.Connection.BeginTransaction();
            IsDisposingTransaction = true;
            return this;
        }

        public DbCommandWrapper CommitTransaction()
        {
            CheckIsIntransaction();
            command.Transaction.Commit();
            return this;
        }

        public DbCommandWrapper RollbackTransaction()
        {
            CheckIsIntransaction();
            command.Transaction.Rollback();
            return this;
        }

        private void CheckIsNotInTransaction()
        {
            if (command != null && command.Transaction != null)
            {
                throw new DbCommandWrapperException("命令已在事务中");
            }
        }

        private void CheckIsIntransaction()
        {
            if (command == null || command.Transaction == null)
            {
                throw new DbCommandWrapperException("命令不在事务中");
            }
        }
        #endregion

        #region 添加参数
        public DbCommandWrapper ClearParameters()
        {
            command.Parameters.Clear();
            return this;
        }

        public DbCommandWrapper AddParameter(DbParameter parameter)
        {
            if (parameter != null)
            {
                command.Parameters.Add(parameter);
            }
            return this;
        }

        public DbCommandWrapper AddParameter(params DbParameter[] parameters)
        {
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            return this;
        }

        public DbCommandWrapper AddParameter(string name, object value)
        {
            DbParameter parameter = CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value == null ? DBNull.Value : value;
            return AddParameter(parameter);
        }

        /// <summary>
        /// 如果参数名已经存在，则重新设置参数的值。若参数不存在则添加。
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public DbCommandWrapper SetParameter(string name, object value)
        {
            if (!command.Parameters.Contains(name))
            {
                command.Parameters[name].Value
                    = value == null ? DBNull.Value : value;
            }
            else
            {
                AddParameter(name, value);
            }

            return this;
        }

        /// <summary>
        /// 删除指定的参数
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DbCommandWrapper RemoveParameter(string name)
        {
            if (command.Parameters.Contains(name))
            {
                command.Parameters.RemoveAt(name);
            }
            return this;
        }
        #endregion

        #region 执行命令
        public virtual DataSet ExecuteDataSet()
        {
            return ExecuteDataSet((DataSet)null);
        }

        public virtual DataSet ExecuteDataSet(DataSet dataSet,
            params string[] tableNames)
        {
            OpenConnection();
            using (DbDataAdapter adapter = CreateDataAdapter())
            {
                adapter.SelectCommand = command;

                if (tableNames != null && tableNames.Length > 0)
                {
                    string tableName = "Table";
                    for (int i = 0; i < tableNames.Length; i++)
                    {
                        if (string.IsNullOrEmpty(tableNames[i]))
                        {
                            continue;
                        }
                        adapter.TableMappings.Add(tableName, tableNames[i]);
                        tableName = "Table" + (i + 1).ToString();
                    }
                }

                if (dataSet == null)
                {
                    dataSet = new DataSet();
                }
                adapter.Fill(dataSet);
                return dataSet;
            }
        }

        public virtual DataSet ExecuteDataSet(params string[] tableNames)
        {
            return ExecuteDataSet(new DataSet(), tableNames);
        }

        public virtual DataTable ExecuteDataTable()
        {
            OpenConnection();
            using (DbDataAdapter adapter = CreateDataAdapter())
            {
                adapter.SelectCommand = command;
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
        }

        /// <summary>
        /// 返回所获取的数据表的第1行。
        /// 如果数据表为空或没有第1行，则返回null。
        /// </summary>
        /// <returns></returns>
        public virtual DataRow ExecuteDataRow()
        {
            DataTable table = ExecuteDataTable();
            if (table == null || table.Rows.Count < 1)
            {
                return null;
            }
            else
            {
                return table.Rows[0];
            }
        }

        public virtual object ExecuteScalar()
        {
            OpenConnection();
            return command.ExecuteScalar();
        }

        /// <summary>
        /// 返回查询结果的最左上单元格的值。
        /// 如果没有结果或者最左上单元格值为DbNull，则返回指定的默认值。
        /// </summary>
        /// <typeparam name="T">指定返回的数据类型</typeparam>
        /// <param name="defaultValue">指定无有效查询结果时返回的默认值</param>
        /// <returns>返回值或在无有效查询结果时返回指定的默认值。</returns>
        public virtual T ExecuteScalar<T>(T defaultValue)
        {
            object obj = ExecuteScalar();
            return obj == null || obj == DBNull.Value ? defaultValue : (T)obj;
        }

        /// <summary>
        /// 返回SQL执行结果第一行第一个单元格的值。
        /// </summary>
        /// <typeparam name="T">返回值的类型。
        /// 该类型必须与数据库中返回值的类型一致。
        /// 如果数据库返回的数值类型为值类型，
        /// 请将类型指定为其对应的可空类型</typeparam>
        /// <exception cref="System.InvalidCaseException">
        /// 指定类型与数据库中返回值的类型不一致时引发该异常。
        /// </exception>
        /// <returns>如果SQL执行结果为空，或者最左上列的值为DbNull，返回null；
        /// 否则返回值。</returns>
        public virtual T ExecuteScalar<T>() where T : class
        {
            object obj = ExecuteScalar();
            return obj == null || obj == DBNull.Value ? null : (T)obj;
        }

        public virtual int ExecuteNonQuery()
        {
            OpenConnection();
            return command.ExecuteNonQuery();
        }

        public virtual DbDataReader ExecuteReader()
        {
            OpenConnection();
            DataReader = command.ExecuteReader();
            return DataReader;
        }
        #endregion

        protected virtual void OpenConnection()
        {
            if (command == null)
            {
                SetCommand();
            }

            if (command.Connection == null)
            {
                command.Connection = CreateConnection();
            }

            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
                IsDisposingConnection = true;
            }
        }

        #region IDisposable 成员
        public void Dispose()
        {
            if (command != null)
            {
                if (IsDisposingTransaction && command.Transaction != null)
                {
                    command.Transaction.Dispose();
                }

                if (command.Connection != null && IsDisposingConnection)
                {
                    command.Connection.Dispose();
                }

                command.Transaction = null;
                command.Connection = null;
                command.Parameters.Clear();
                command.Dispose();
            }
        }
        #endregion
    }

    public class DbCommandWrapperException : DbException
    {
        public DbCommandWrapperException()
        {
        }

        public DbCommandWrapperException(string message)
            : base(message)
        {
        }

        public DbCommandWrapperException(string message, Exception inner)
            : base(message, inner)
        {
        }
    }
}
